Configuring: Database Table

Select the data source containing the data table to be imported. The table will be converted into a dataset. The fields and rows of the new dataset will be determined by the selected query.

Example of configuring: Database table

Steps to configure

  1. Data source: Select the data source from the listed providers and enter the security permissions. Define how to import the timestamps.

  2. Field selection: Specify the table and the fields to be included in the query. Select whether to use an automatically generated SELECT query, or whether to create a custom SELECT query.

  3. Configure import query: Select the table and fields to be used and enter the query.

  4. Execution: A dataset will be created from the query and data source entered.

Step 1: Data source

Set up the connection to the data source by selecting a provider, entering the server name and login details and selecting the database to use. Also define how to import the timestamps.

When you read data from a table that contains a DATETIME2 field, you must use the “SQL Server Native Client” OLE DB provider for that field to be recognized as a timestamp field. The “Microsoft OLE DB Provider for SQL Server” and “… for ODBC Drivers” will not work (not support by Microsoft) and will recognize DATETIME2 as a string.

  • Click on [Get Providers] to list all the available data sources.

SQL provider (e.g. SQLOLEDB)

  • The server name will be the name of your SQL server.

  • Once you have selected the database, click on [Test Connection].

Oracle provider (e.g. OraOLEDB.Oracle)

  • The server name will be the name of your Oracle server.

  • Once you have selected the database, click on [Test Connection].

ODBC provider (MSDASQL)

  • Select a driver or the DSN from the drop down list in the [OCBC Driver] text box.

  • Selecting DSN:

    • select from the list of user DSNs from the drop down list lower on the page.

  • Selecting a driver:

    • enter the server name where you enter database name for the SQL Provider, and the database name in the edit box just below it.

  • Default: Settings are not configured

  • Minimum configuration: Connection to a database that can be tested.

Access OleDb providers (e.g. Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0)

  • The Access File is the name of the file where the Access database is saved.

  • Log in with your username and password if the Access file requires this.

Test Connection page:

You will only be taken to this page if the connection to a database was not valid. All the errors related to the connection will be listed here. You need to return to the previous page to fix these errors. After you fixed the errors, click on [Test Connection] again.

Timestamps
  • Select the radio button that will define the type of all of the imported timestamp fields.

    • Options include importing from the local time zone, UTC time zone or a custom time zone. Select the time zone from the drop down list.

    • The timestamps being imported (from the selected database) will be interpreted as being from the selected time zone.

  • Default: Local time is selected.

  • Minimum configuration: One timestamp type must be selected.

Step 2: Field selection

Select a table from the drop down list and select the fields related to that table that you want to include in the query. These selected fields will be used to generate the T-SQL SELECT query.

There is an option to either automatically generate a query (that cannot be edited), or create a custom query manually, which can be edited, but where the pre-populated field and table selection is disabled.

NOTE: Unsupported data types are converted to string data, where possible. If data values cannot be converted to string values, null values will be inserted instead.

  • Generate SELECT query checkbox: This influences the functionality of the Configure Import Query page.

    • When the checkbox is CHECKED: An automated query will be generated. Select a table and fields from the Field Selection page and the automatically generated query will show on the Configure Import Query page. When a query is generated this way, the [Verify Query] button on the Configure Import Query page is DISABLED and the edit box displaying the query is READ-ONLY - no editing can be done.

    • When the checkbox is UNCHECKED: A custom query needs to be created on the Configure Import Query page. On the Field Selection page, the table selection and field selection is DISABLED, but the query on the Configure Import Query page is editable and the [Verify Query] button is always ENABLED. Any changes made to the query when manually editing the query will require verification again, by clicking [Verify Query] again.

  • Table: Select the correct SQL table.

  • Fields: Fields from this table will populate the Available Fields pane. Use the arrow keys to select the required fields.

  • These selected fields will be used to Generate the T-SQL SELECT query.

  • Default: No table is selected.

  • Minimum configuration: When automatically generating a SELECT query: A table must be selected, and at least one field.

NOTE:For users of software versions older than Proficy CSense 5.5: If previous projects contain database queries where added columns were of unsupported data types, then when reconfiguring the operation in this software version, you will have to manually remove these in the operation query.

Step 3: Configure import query

Generate or verify the query to define what is selected from the database.

  • Automatically generated SELECT query: An automated query will be generated and will populate the Configure Import Query page. This query cannot be edited, the query is read only, and the [Verify Query] button is DISABLED.

  • Custom generated SELECT query: A SELECT query needs to be created on the Configure Import Query page. Create and edit the SELECT query as required, and [Verify Query] to check if the query is valid. Any further changes always require verification again.

  • The query should not contain commands that will alter a table. These are "DELETE", "INSERT", "CREATE", "UPDATE", "ALTER". If any of these are contained in the query it will be considered invalid.

  • All errors in the query will be listed underneath the query window.

  • Default: The table and fields selected will populate the query window.

  • Minimum configuration: A valid query must be in the query edit box.

Step 4: Execution

The table from the database will be added as a dataset. The fields and rows in this dataset will be determined by the query that you entered or generated.


Related topics:

  

CSense 2023- Last updated: June 24,2025